// © 2009 Microsoft Corporation.  All rights reserved.
# region Namespaces

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web.UI.WebControls;
using System.Xml;

using Microsoft.InformationSecurity.Risk.ResourceMessages;
using Microsoft.Practices.EnterpriseLibrary.Validation;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.InformationSecurity.Risk.DataEntity;
using Microsoft.InformationSecurity.Risk.RiskDataContracts;
using Microsoft.InformationSecurity.ISRM.Risk.DataEntity;
using System.Configuration;
using System.Collections;
using System.Collections.Specialized;
using System.Security.Permissions;

#endregion

namespace Microsoft.InformationSecurity.Risk.RiskDataLayer
{
    public static class ManageRisk
    {
        /// <summary>
        /// Gets all the PII collection from system.
        /// </summary>
        /// <returns></returns>
        public static GeneralStats GetGeneralStats()
        {
            GeneralStats stats = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            
            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_GetGeneralISRMStats");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                stats = new GeneralStats();

                if (sqldataReader.Read())
                {
                    stats.TotalRiskInSystem = (int)sqldataReader.GetValue(0);
                    stats.TotalNewRiskInLast7Days= (int)sqldataReader.GetValue(1);
                    stats.TotalNewRiskInLast30Days = (int)sqldataReader.GetValue(2);
                    stats.TotalActiveRisk  = (int)sqldataReader.GetValue(3);
                    stats.TotalNewInLastTasks7Days = (int)sqldataReader.GetValue(4);
                    stats.TotalNewInLastTasks30Days = (int)sqldataReader.GetValue(5);              
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return stats;
        }

        /// <summary>
        /// Gets all the PII collection from system.
        /// </summary>
        /// <returns></returns>
        public static Collection<ListItem> GetPIIOptions()
        {
            ListItem lstTemp = null;
            Database database = null;
            SqlDataReader sqldataReader = null;

            Collection<ListItem> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetPIIClassifications");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<ListItem>();

                while (sqldataReader.Read())
                {
                    lstTemp = new ListItem();
                    lstTemp.Text = sqldataReader.GetString(1);
                    lstTemp.Value = sqldataReader.GetValue(0).ToString();

                    listCollection.Add(lstTemp);

                    lstTemp = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Gets all the Risk Group collection from system.
        /// </summary>
        /// <returns></returns>
        public static Collection<ListItem> GetRiskGroups()
        {
            ListItem lstTemp = null;
            Database database = null;
            SqlDataReader sqldataReader = null;

            Collection<ListItem> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRiskGroups");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<ListItem>();

                while (sqldataReader.Read())
                {
                    lstTemp = new ListItem();
                    lstTemp.Text = sqldataReader.GetString(1);
                    lstTemp.Value = sqldataReader.GetValue(0).ToString();

                    listCollection.Add(lstTemp);

                    lstTemp = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }


        /// <summary>
        /// Gets all the ERM collection from system.
        /// </summary>
        /// <returns></returns>
        public static Collection<ListItem> GetOERMClassificationOptions()
        {
            ListItem lstTemp = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<ListItem> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetOERMClassifications");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<ListItem>();

                while (sqldataReader.Read())
                {
                    lstTemp = new ListItem();
                    lstTemp.Text = sqldataReader.GetString(1);
                    lstTemp.Value = sqldataReader.GetValue(0).ToString();

                    listCollection.Add(lstTemp);
                    lstTemp = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Gets all the Data Classifications from system.
        /// </summary>
        /// <returns></returns>
        public static Collection<ListItem> GetDataClassificationOptions()
        {
            ListItem lstTemp = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<ListItem> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetDataClassifications");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<ListItem>();

                while (sqldataReader.Read())
                {
                    lstTemp = new ListItem();
                    lstTemp.Text = sqldataReader.GetString(1);
                    lstTemp.Value = sqldataReader.GetValue(0).ToString();

                    listCollection.Add(lstTemp);
                    lstTemp = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Gets all the Locations collection from system.
        /// </summary>
        /// <returns></returns>
        public static Collection<ListItem> GetLocationOptions()
        {
            ListItem lstTemp = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<ListItem> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetLocations");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<ListItem>();

                while (sqldataReader.Read())
                {
                    lstTemp = new ListItem();
                    lstTemp.Text = sqldataReader.GetString(1) + "-" + sqldataReader.GetString(2);
                    lstTemp.Value = sqldataReader.GetValue(0).ToString();

                    listCollection.Add(lstTemp);
                    lstTemp = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>        
        /// Gets all the Notes from system for a specific Risk.        
        /// </summary>
        /// <param name="riskID">Risk ID</param>
        /// <returns></returns>
        public static Collection<NoteData> GetNotes(int riskID)
        {
            NoteData noteInstance = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<NoteData> listCollection = null;

            try
            {
                if (riskID < 1)
                    throw new ArgumentException("SR_20001");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetNotes");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter
                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskID;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<NoteData>();

                while (sqldataReader.Read())
                {
                    noteInstance = new NoteData();
                    noteInstance.Note = string.Empty;
                    noteInstance.SubmittedByFullName = sqldataReader.GetString(1);
                    noteInstance.CreatedDate = sqldataReader.GetValue(2).ToString();
                    listCollection.Add(noteInstance);

                    noteInstance = null;
                    noteInstance = new NoteData();
                    noteInstance.Note = sqldataReader.GetString(3);
                    noteInstance.CreatedDate = string.Empty;
                    noteInstance.SubmittedByFullName = string.Empty;
                    listCollection.Add(noteInstance);
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Returns the list of Risk origin options.
        /// </summary>
        /// <returns></returns>
        public static Collection<ListItem> GetRiskOrigin()
        {
            ListItem lstTemp = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<ListItem> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRiskOrigin");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<ListItem>();

                while (sqldataReader.Read())
                {
                    lstTemp = new ListItem();
                    lstTemp.Text = sqldataReader.GetString(1);
                    lstTemp.Value = sqldataReader.GetValue(0).ToString();

                    listCollection.Add(lstTemp);
                    lstTemp = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Returns the list of Risk status Options.
        /// </summary>
        /// <returns></returns>
        public static Collection<ListItem> GetRiskStatusOptions()
        {
            ListItem lstTemp = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<ListItem> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRiskStatusOptions");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<ListItem>();

                while (sqldataReader.Read())
                {
                    lstTemp = new ListItem();
                    lstTemp.Text = sqldataReader.GetString(1);
                    lstTemp.Value = sqldataReader.GetValue(0).ToString();

                    listCollection.Add(lstTemp);
                    lstTemp = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Returns the list of Risk Impact Options from system.
        /// </summary>
        /// <returns></returns>
        public static Collection<ListItem> GetRiskImpactOptions()
        {
            ListItem lstTemp = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<ListItem> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRiskImpactData");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<ListItem>();

                while (sqldataReader.Read())
                {
                    lstTemp = new ListItem();
                    lstTemp.Text = sqldataReader.GetValue(2).ToString() + " - " + sqldataReader.GetString(1);
                    lstTemp.Value = sqldataReader.GetValue(0).ToString();

                    listCollection.Add(lstTemp);
                    lstTemp = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Returns the list of Risk Frequency Options.
        /// </summary>
        /// <returns></returns>
        public static Collection<ListItem> GetRiskFrequencyOptions()
        {
            ListItem lstTemp = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<ListItem> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRiskFrequencyData");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<ListItem>();

                while (sqldataReader.Read())
                {
                    lstTemp = new ListItem();
                    lstTemp.Text = sqldataReader.GetValue(2).ToString() + " - " + sqldataReader.GetString(1);
                    lstTemp.Value = sqldataReader.GetValue(0).ToString();

                    listCollection.Add(lstTemp);
                    lstTemp = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Returns the list of Risk Control Affectiveness Options from system.
        /// </summary>
        /// <returns></returns>
        public static Collection<ListItem> GetRiskControlAffectivenessOptions()
        {
            ListItem lstTemp = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<ListItem> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRiskControlEffectivenessData");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<ListItem>();

                while (sqldataReader.Read())
                {
                    lstTemp = new ListItem();
                    lstTemp.Text = sqldataReader.GetValue(2).ToString() + " - " + sqldataReader.GetString(1);
                    lstTemp.Value = sqldataReader.GetValue(0).ToString();
                    listCollection.Add(lstTemp);
                    lstTemp = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

       
        /// <summary>
        /// Submit new Risk into system.
        /// </summary>
        /// <param name="riskInstance"> Risk Object</param>
        /// <param name="submittedbyFullName">Full Name of the person who submitted the risk.</param>
        /// <returns></returns>
        public static int SubmitRisk(RiskData riskInstance, out string submittedbyFullName)
        {
            Database database = null;
            int riskID = -1;
            try
            {
                // Check for risk is not null.
                if (riskInstance == null)
                    throw new ArgumentException("SR_20004");

                // Check for Validation using for Validation rules defined for Risk Object.
                ValidationResults validationResults = Validation.Validate<RiskData>(riskInstance, "RiskDataRules");
                if (!validationResults.IsValid)
                    throw new ArgumentException("SR_20003");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spInsertRisk");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter

                sqlCmd.Parameters.Add("@title", SqlDbType.NVarChar, 250).Value = riskInstance.Title;
                sqlCmd.Parameters.Add("@desc", SqlDbType.NVarChar).Value = riskInstance.Desc;
                sqlCmd.Parameters.Add("@riskorigin", SqlDbType.Int).Value = riskInstance.RiskOrigin;
                sqlCmd.Parameters.Add("@submittedBy", SqlDbType.Int).Value = riskInstance.SubmittedBy;
                sqlCmd.Parameters.Add("@currentRiskImapact", SqlDbType.Int).Value = riskInstance.CurrentImpactValue;
                sqlCmd.Parameters.Add("@currentRiskFrequency", SqlDbType.Int).Value = riskInstance.CurrentFrequencyValue;
                sqlCmd.Parameters.Add("@currentRiskCF", SqlDbType.Int).Value = riskInstance.CurrentControlAffectivenessValue;
                sqlCmd.Parameters.Add("@targetRiskImapact", SqlDbType.Int).Value = riskInstance.TargetImpactValue;
                sqlCmd.Parameters.Add("@targetRiskFrequency", SqlDbType.Int).Value = riskInstance.TargetFrequencyValue;
                sqlCmd.Parameters.Add("@targetRiskCF", SqlDbType.Int).Value = riskInstance.TargetControlAffectivenessValue;

                sqlCmd.Parameters.Add("@targetRiskrating", SqlDbType.NVarChar, 50).Value = riskInstance.TargetRiskRating;
                sqlCmd.Parameters.Add("@currentRiskrating", SqlDbType.NVarChar, 50).Value = riskInstance.CurrentRiskRating;

                // Target risk calculated value.
                if (riskInstance.TargetCalculatedRiskValue == 0)
                    sqlCmd.Parameters.Add("@targetCalculatedRiskValue", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@targetCalculatedRiskValue", SqlDbType.Int).Value = riskInstance.TargetCalculatedRiskValue;

                // Current risk calculated value.
                if (riskInstance.CurrentCalculatedRiskValue == 0)
                    sqlCmd.Parameters.Add("@currentCalculatedRiskValue", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@currentCalculatedRiskValue", SqlDbType.Int).Value = riskInstance.CurrentCalculatedRiskValue;
                if (riskInstance.RiskManagerID != -1)
                    sqlCmd.Parameters.Add("@riskManagerID", SqlDbType.Int).Value = riskInstance.RiskManagerID;
                else
                    sqlCmd.Parameters.Add("@riskManagerID", SqlDbType.Int).Value = null;

                sqlCmd.Parameters.Add("@riskValidatedByTriage", SqlDbType.Int).Value = riskInstance.RiskValidatedByTriage;
                sqlCmd.Parameters.Add("@note", SqlDbType.NVarChar).Value = riskInstance.Note;
                sqlCmd.Parameters.Add("@oeramClassification", SqlDbType.NVarChar).Value = riskInstance.OERMClassifications;
                sqlCmd.Parameters.Add("@organizations", SqlDbType.NVarChar).Value = riskInstance.Organizations;
                sqlCmd.Parameters.Add("@locations", SqlDbType.NVarChar).Value = riskInstance.Locations;
                sqlCmd.Parameters.Add("@contacts", SqlDbType.NVarChar).Value = riskInstance.Contacts;
                sqlCmd.Parameters.Add("@Alias", SqlDbType.NVarChar, 50).Value = riskInstance.Alias;
                sqlCmd.Parameters.Add("@piid", SqlDbType.Int).Value = riskInstance.PIIID;
                sqlCmd.Parameters.Add("@dataClassfication", SqlDbType.Int).Value = riskInstance.DataClassificationID;
                sqlCmd.Parameters.Add("@Status", SqlDbType.Int).Value = riskInstance.Status;
                sqlCmd.Parameters.Add("@riskOwnerComment", SqlDbType.NVarChar).Value = riskInstance.OwnersComment;
                sqlCmd.Parameters.Add("@personID", SqlDbType.NVarChar).Value = riskInstance.SubmittedBy;
                sqlCmd.Parameters.Add("@currentRiskImapactJustification", SqlDbType.NVarChar).Value = riskInstance.CurrentImapctJustification;
                sqlCmd.Parameters.Add("@currentRiskFrequencyJustification", SqlDbType.NVarChar).Value = riskInstance.CurrentFrequencyJustification;
                sqlCmd.Parameters.Add("@currentRiskCFJustification", SqlDbType.NVarChar).Value = riskInstance.CurrentControlAffectivenessJustification;
                sqlCmd.Parameters.Add("@targetRiskImapactJustification", SqlDbType.NVarChar).Value = riskInstance.TargetImpactJustification;
                sqlCmd.Parameters.Add("@targetRiskFrequencyJustification", SqlDbType.NVarChar).Value = riskInstance.TargetFrequencyJustification;
                sqlCmd.Parameters.Add("@targetRiskCFJustification", SqlDbType.NVarChar).Value = riskInstance.TargetControlAffectivenessJustification;
                sqlCmd.Parameters.Add("@riskManagerflagForConfiguration", SqlDbType.Bit).Value = riskInstance.RiskConfigurationFlag;
                sqlCmd.Parameters.Add("@riskGroups", SqlDbType.NVarChar).Value = riskInstance.RiskGroup;
                sqlCmd.Parameters.Add("@riskIDref", SqlDbType.Int).Direction = ParameterDirection.Output;
                // CR 06/08/2009. Added new fields for prioritizationWt and prioritizationCRV.
                sqlCmd.Parameters.Add("@prioritizationWt", SqlDbType.Decimal).Value = riskInstance.RiskPriortizedWt;
                if (riskInstance.RiskPriortizedCRV != -1)
                    sqlCmd.Parameters.Add("@prioritizationCRV", SqlDbType.Decimal).Value = riskInstance.RiskPriortizedCRV;
                else
                    sqlCmd.Parameters.Add("@prioritizationCRV", SqlDbType.Decimal).Value = null;

                sqlCmd.Parameters.Add("@submitedbyFullName", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output;

                database.ExecuteNonQuery(sqlCmd);
                // Return RISK ID.
                riskID = (int)sqlCmd.Parameters["@riskIDref"].Value;
                submittedbyFullName = (string)sqlCmd.Parameters["@submitedbyFullName"].Value;
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (database != null)
                    database = null;
            }
            return riskID;
        }

        /// <summary>
        /// Get Risk Manager.
        /// </summary>
        /// <param name="riskID">RiskID</param>
        /// <returns></returns>
        public static ListItem GetRiskManager(int riskID)
        {
            ListItem manager = null;
            Database database = null;
            SqlDataReader sqldataReader = null;

            try
            {
                if (riskID < 1)
                    throw new ArgumentException("SR_20001");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_GetRiskManager");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter
                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskID;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);

                if (sqldataReader.Read())
                {
                    manager = new ListItem();
                    manager.Value = sqldataReader.GetValue(0).ToString();
                    manager.Text = sqldataReader.GetString(1);
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return manager;
        }

        /// <summary>
        /// Get Master History data from system.
        /// </summary>
        /// <param name="riskID"></param>
        /// <returns></returns>
        public static Collection<History> GetMasterHistoryData(int riskID)
        {
            History historyInstance = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<History> listCollection = null;

            try
            {
                if (riskID < 1)
                    throw new ArgumentException("SR_20001");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetHistoryMaster");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter
                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskID;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<History>();

                while (sqldataReader.Read())
                {
                    historyInstance = new History();
                    historyInstance.CreatedDate = (DateTime)sqldataReader.GetValue(2);
                    historyInstance.PersonID = (int)sqldataReader.GetValue(0);
                    historyInstance.FullName = sqldataReader.GetString(1);
                    historyInstance.Ticks = ((DateTime)sqldataReader.GetValue(2)).Ticks;
                    listCollection.Add(historyInstance);
                    historyInstance = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Get the details of History data based on master History record.
        /// </summary>
        /// <param name="riskID"></param>
        /// <param name="createdDate"></param>
        /// <param name="personID"></param>
        /// <returns>Collection of Custom Data Entity</returns>
        public static Collection<HistoryDetail> GetDetailsHistoryData(int riskID, DateTime createdDate, int personID)
        {

            HistoryDetail historyDetailInstance = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<HistoryDetail> listCollection = null;

            try
            {
                if (riskID < 1)
                    throw new ArgumentException("SR_20001");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetHistoryDetail");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter
                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskID;
                sqlCmd.Parameters.Add("@personID", SqlDbType.Int).Value = personID;
                sqlCmd.Parameters.Add("@createdDate", SqlDbType.DateTime).Value = createdDate;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<HistoryDetail>();

                while (sqldataReader.Read())
                {
                    historyDetailInstance = new HistoryDetail();
                    historyDetailInstance.FieldName = sqldataReader.GetString(0);
                    historyDetailInstance.OldValue = sqldataReader.GetString(1);
                    historyDetailInstance.NewValue = sqldataReader.GetString(2);

                    listCollection.Add(historyDetailInstance);
                    historyDetailInstance = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// To get the list of all the incidents from the system.
        /// </summary>
        /// <param name="riskID"></param>
        /// <returns></returns>
        public static Collection<Incident> GetIncidentList(int riskID, string dir, string sort)
        {
            Incident incidentInstance = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<Incident> listCollection = null;

            try
            {
                if (riskID < 1)
                    throw new ArgumentException("SR_20001");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetIncidents");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter
                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskID;
                sqlCmd.Parameters.Add("@sort", SqlDbType.NVarChar, 50).Value = sort;
                sqlCmd.Parameters.Add("@dir", SqlDbType.NVarChar).Value = dir;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<Incident>();

                while (sqldataReader.Read())
                {
                    incidentInstance = new Incident();
                    incidentInstance.Title = sqldataReader.GetString(1);
                    incidentInstance.IncidentID = (int)sqldataReader.GetValue(0);
                    incidentInstance.SubmittedBy = sqldataReader.GetString(3);
                    incidentInstance.SubmittedDate = (DateTime)sqldataReader.GetValue(2);

                    if (!sqldataReader.IsDBNull(4))
                        incidentInstance.UpdatedBy = sqldataReader.GetString(4);
                    else
                        incidentInstance.UpdatedBy = string.Empty;

                    if (!sqldataReader.IsDBNull(5))
                        incidentInstance.UpdatedDate = sqldataReader.GetValue(5).ToString();
                    else
                        incidentInstance.UpdatedDate = string.Empty;

                    listCollection.Add(incidentInstance);
                    incidentInstance = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }


        /// <summary>
        /// To get single incident from the system.
        /// </summary>
        /// <param name="riskID">RiskID associated in incident.</param>
        /// <param name="incidentRiskID"></param>
        /// <returns>Incident Object</returns>
        public static Incident GetIncidentData(int riskID, int incidentRiskID)
        {
            Incident incidentInstance = null;
            Database database = null;
            SqlDataReader sqldataReader = null;

            try
            {
                if (riskID < 1)
                    throw new ArgumentException("SR_20001");

                if (incidentRiskID < 1)
                    throw new ArgumentException("SR_20002");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetSingleIncident");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter
                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskID;
                sqlCmd.Parameters.Add("@riskIncidentID", SqlDbType.Int).Value = incidentRiskID;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                if (sqldataReader.Read())
                {
                    incidentInstance = new Incident();

                    if (!sqldataReader.IsDBNull(6))
                        incidentInstance.Desc = sqldataReader.GetString(6);
                    else
                        incidentInstance.Desc = string.Empty;

                    if (!sqldataReader.IsDBNull(4))
                        incidentInstance.UpdatedBy = sqldataReader.GetString(4);
                    else
                        incidentInstance.UpdatedBy = string.Empty;

                    if (!sqldataReader.IsDBNull(5))
                        incidentInstance.UpdatedDate = sqldataReader.GetValue(5).ToString();
                    else
                        incidentInstance.UpdatedDate = string.Empty;

                    incidentInstance.Title = sqldataReader.GetString(1);
                    incidentInstance.IncidentID = (int)sqldataReader.GetValue(0);
                    incidentInstance.SubmittedBy = sqldataReader.GetString(3);
                    incidentInstance.SubmittedDate = (DateTime)sqldataReader.GetValue(2);
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return incidentInstance;
        }

        /// <summary>
        /// To update single Incident in the system.
        /// </summary>        
        /// <returns></returns>
        public static void UpdateIncident(int riskID, string desc, string title, int personID, int riskIncidentID)
        {
            Database database = null;
            try
            {
                if (riskID < 1)
                    throw new ArgumentException("SR_20001");

                if (riskIncidentID < 1)
                    throw new ArgumentException("SR_20002");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spUpdateIncident");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter
                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskID;
                sqlCmd.Parameters.Add("@riskIncidentID", SqlDbType.Int).Value = riskIncidentID;
                sqlCmd.Parameters.Add("@updatedby", SqlDbType.Int).Value = personID;
                sqlCmd.Parameters.Add("@title", SqlDbType.NVarChar, 100).Value = title;
                sqlCmd.Parameters.Add("@desc", SqlDbType.NVarChar, 500).Value = desc;

                database.ExecuteScalar(sqlCmd);
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (database != null)
                    database = null;
            }
        }
        /// <summary>
        /// This method to insert incident in the system.
        /// </summary>        
        /// <returns></returns>        
        public static void InsertIncident(int riskID, string desc, string title, int personID)
        {            
            Database database = null;
            try
            {
                if (riskID < 1)
                    throw new ArgumentException("SR_20001");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spInsertIncident");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter
                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskID;
                sqlCmd.Parameters.Add("@personID", SqlDbType.Int).Value = personID;
                sqlCmd.Parameters.Add("@title", SqlDbType.NVarChar, 100).Value = title;
                sqlCmd.Parameters.Add("@desc", SqlDbType.NVarChar).Value = desc;

                database.ExecuteScalar(sqlCmd);
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (database != null)
                    database = null;
            }
        }

        /// <summary>
        /// Gets the data set for the chart display.
        /// </summary>
        /// <returns></returns>
        public static DataSet GetCalculatedCRVDataSet(int riskID, int flagforFilter)
        {
            DataSet dsPoints = null;
            Database database = null;

            try
            {
                if (riskID < 1)
                    throw new ArgumentException("SR_20001");


                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetHistoryCurrentCRV");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter
                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskID;
                sqlCmd.Parameters.Add("@filter", SqlDbType.Int).Value = flagforFilter;

                dsPoints = database.ExecuteDataSet(sqlCmd);
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (database != null)
                    database = null;
            }
            return dsPoints;
        }

        /// <summary>
        /// Get Root node for the organization tree.
        /// </summary>
        /// <returns></returns>
        public static Collection<OrganizationData> GetRootLevel()
        {
            OrganizationData orgInstance = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<OrganizationData> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRootLevel");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<OrganizationData>();

                while (sqldataReader.Read())
                {
                    orgInstance = new OrganizationData();
                    orgInstance.OrgnaizationID = (int)sqldataReader.GetValue(0);
                    orgInstance.OrgnaizationName = sqldataReader.GetString(1);
                    orgInstance.OrgnaizationCode = sqldataReader.GetString(2);

                    listCollection.Add(orgInstance);
                    orgInstance = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Get the sub organization based on specific organization Id.
        /// </summary>
        /// <param name="organizationID"></param>
        /// <returns></returns>
        public static Collection<OrganizationData> GetNextLevelOrganizations(int organizationID)
        {
            OrganizationData orgInstance = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<OrganizationData> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetNextLevelOrganization");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@nodeID", SqlDbType.Int).Value = organizationID;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<OrganizationData>();

                while (sqldataReader.Read())
                {
                    orgInstance = new OrganizationData();
                    orgInstance.OrgnaizationID = (int)sqldataReader.GetValue(0);
                    orgInstance.OrgnaizationName = sqldataReader.GetString(1);
                    orgInstance.OrgnaizationCode = sqldataReader.GetString(2);

                    listCollection.Add(orgInstance);
                    orgInstance = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// To get TopN aliases based on characters to filter.
        /// </summary>
        /// <param name="alias"></param>
        /// <returns></returns>
        public static Collection<string> GetTOPNAliases(string alias)
        {
            string aliasInstance = string.Empty;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Collection<String> listCollection = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_GetTopNContacts");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@alias", SqlDbType.NVarChar, 50).Value = alias;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                listCollection = new Collection<String>();

                while (sqldataReader.Read())
                {
                    aliasInstance = sqldataReader.GetString(0);
                    aliasInstance = aliasInstance + " (" + sqldataReader.GetString(1) + ") ";
                    listCollection.Add(aliasInstance);
                    aliasInstance = string.Empty;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return listCollection;
        }

        /// <summary>
        /// Get Organization Contact XML based on organization selected and people selected.
        /// </summary>
        /// <param name="orgaizationlist"></param>
        /// <param name="personlist"></param>
        /// <returns></returns>
        public static OrganizationContact GetOrganizationContactXML(string orgaizationlist, string personlist)
        {
            OrganizationContact organizationContact = null;
            Database database = null;
            SqlDataReader sqldataReader = null;
            Person personInstance = null;
            Collection<Person> collectionList = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetOrganizationContactXML");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@organization", SqlDbType.NVarChar).Value = orgaizationlist;
                sqlCmd.Parameters.Add("@contacts", SqlDbType.NVarChar).Value = personlist;
                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                collectionList = new Collection<Person>();
                organizationContact = new OrganizationContact();
                StringBuilder holdXML = new StringBuilder(5000);

                if (sqldataReader.HasRows)
                {
                    while (sqldataReader.Read())
                    {
                        if (!sqldataReader.IsDBNull(0))
                        {
                            holdXML.Append(sqldataReader.GetString(0));
                        }
                    }
                    if (0 == holdXML.Length)
                        organizationContact.OrganizationContactXML = string.Empty;
                    else
                        organizationContact.OrganizationContactXML = holdXML.ToString();
                }

                if (sqldataReader.NextResult())
                {
                    while (sqldataReader.Read())
                    {
                        personInstance = new Person();
                        personInstance.PersonID = (int)sqldataReader.GetValue(1);
                        personInstance.FullName = sqldataReader.GetString(0);
                        collectionList.Add(personInstance);
                        personInstance = null;
                    }
                }
                organizationContact.OrphanedContacts = collectionList;
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return organizationContact;
        }

        /// <summary>
        /// Get the personID for a specific alias.
        /// </summary>
        /// <param name="alias"></param>
        /// <returns></returns>
        public static object GetPersonID(string alias)
        {
            object personID;
            Database database = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetPersonIdentifier");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@alias", SqlDbType.NVarChar, 50).Value = alias;

                personID = database.ExecuteScalar(sqlCmd);
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (database != null)
                    database = null;
            }
            return personID;
        }

        /// <summary>
        /// Get Current Risk Value from system.
        /// </summary>
        /// <param name="impact"></param>
        /// <param name="frequency"></param>
        /// <param name="controlAffectiveness"></param>
        /// <returns></returns>
        public static object GetCRV(int impact, int frequency, int controlAffectiveness)
        {
            object CRV = null;
            Database database = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetCRV");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                if (impact == 0)
                    sqlCmd.Parameters.Add("@impact", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@impact", SqlDbType.Int).Value = impact;

                if (frequency == 0)
                    sqlCmd.Parameters.Add("@frequency", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@frequency", SqlDbType.Int).Value = frequency;

                if (controlAffectiveness == 0)
                    sqlCmd.Parameters.Add("@controlAffectiveness", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@controlAffectiveness", SqlDbType.Int).Value = controlAffectiveness;

                CRV = database.ExecuteScalar(sqlCmd);

            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (database != null)
                    database = null;
            }

            if (CRV.Equals(System.DBNull.Value))
                return null;
            else
                return CRV;
        }

        /// <summary>
        /// To get Prirotized Current Risk Value from the system for specific Risk.
        /// </summary>
        /// <param name="crv"></param>
        /// <param name="wt"></param>
        /// <returns></returns>
        public static object GetPCRV(int crv, Decimal wt)
        {
            object PCRV = null;
            Database database = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spPrioritizedCRV");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                if (crv == 0)
                    sqlCmd.Parameters.Add("@crv", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@crv", SqlDbType.Int).Value = crv;

                if (wt == 0)
                    sqlCmd.Parameters.Add("@wt", SqlDbType.Decimal).Value = null;
                else
                    sqlCmd.Parameters.Add("@wt", SqlDbType.Decimal).Value = wt;

                PCRV = database.ExecuteScalar(sqlCmd);

            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (database != null)
                    database = null;
            }

            if (PCRV.Equals(System.DBNull.Value))
                return null;
            else
                return PCRV;
        }

        /// <summary>
        /// To get the Risk rating for the Specific Risk.
        /// </summary>
        /// <param name="impact"></param>
        /// <param name="frequency"></param>
        /// <param name="controlAffectiveness"></param>
        /// <returns></returns>
        public static object GetRiskRating(int impact, int frequency, int controlAffectiveness)
        {
            object RiskRating = null;
            Database database = null;

            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRiskRating");
                sqlCmd.CommandType = CommandType.StoredProcedure;

                if (impact == 0)
                    sqlCmd.Parameters.Add("@impact", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@impact", SqlDbType.Int).Value = impact;

                if (frequency == 0)
                    sqlCmd.Parameters.Add("@frequency", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@frequency", SqlDbType.Int).Value = frequency;

                if (controlAffectiveness == 0)
                    sqlCmd.Parameters.Add("@controlAffectiveness", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@controlAffectiveness", SqlDbType.Int).Value = controlAffectiveness;

                RiskRating = database.ExecuteScalar(sqlCmd);
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (database != null)
                    database = null;
            }
            if (RiskRating.Equals(System.DBNull.Value))
                return null;
            else
                return RiskRating;
        }

        /// <summary>
        /// Get all risk information for the specific riskID.
        /// </summary>
        /// <param name="riskId">riskId</param>
        /// <returns> RiskInformation object</returns>
        public static RiskInformation GetRiskInformation(int riskId)
        {
            Database database = null;
            string textForNotes = string.Empty;
            string textForDelimiter = string.Empty;
            SqlDataReader sqldataReader = null;
            Collection<NoteData> listnoteCollection = null;
            Collection<int> listOERMCollection = null;
            Collection<int> listRegionCollection = null;
            Collection<int> listGroupsCollection = null;
            Collection<Person> listPersonCollection = null;
            OrganizationContact organizationContact = null;
            Person personInstance = null;
            RiskInformation riskInstance = null;
            StringBuilder holdXML = new StringBuilder(5000);

            try
            {
                if (riskId < 1)
                    throw new ArgumentException("SR_20001");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRiskData");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter
                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskId;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);

                if (sqldataReader.HasRows)
                {
                    riskInstance = new RiskInformation();
                    if (sqldataReader.Read())
                    {
                        // MASTER Risk Data.
                        riskInstance.RiskID = (int)sqldataReader.GetValue(0);
                        riskInstance.Title = sqldataReader.GetString(2);
                        riskInstance.Desc = sqldataReader.GetString(3);
                        riskInstance.Status = (int)sqldataReader.GetValue(4);
                        riskInstance.RiskOrigin = (int)sqldataReader.GetValue(5);
                        riskInstance.SubmittedByID = (int)sqldataReader.GetValue(6);
                        riskInstance.SubmittedByFullName = sqldataReader.GetString(7);
                        riskInstance.RiskManagerID = (int)sqldataReader.GetValue(8);
                        riskInstance.CreatedDate = (DateTime)sqldataReader.GetValue(9);

                        // C Impact
                        riskInstance.CurrentImpactValue = (int)sqldataReader.GetValue(10);

                        if (!sqldataReader.IsDBNull(11))
                            riskInstance.CurrentImapctJustification = sqldataReader.GetString(11);
                        else
                            riskInstance.CurrentImapctJustification = string.Empty;

                        //C Control Frequency
                        riskInstance.CurrentFrequencyValue = (int)sqldataReader.GetValue(12);

                        if (!sqldataReader.IsDBNull(13))
                            riskInstance.CurrentFrequencyJustification = sqldataReader.GetString(13);
                        else
                            riskInstance.CurrentFrequencyJustification = string.Empty;

                        // C Control Affectiveness
                        riskInstance.CurrentControlAffectivenessValue = (int)sqldataReader.GetValue(14);

                        if (!sqldataReader.IsDBNull(15))
                            riskInstance.CurrentControlAffectivenessJustification = sqldataReader.GetString(15);
                        else
                            riskInstance.CurrentControlAffectivenessJustification = string.Empty;

                        if (!sqldataReader.IsDBNull(16))
                            riskInstance.CurrentRiskRating = sqldataReader.GetString(16);
                        else
                            riskInstance.CurrentRiskRating = null;

                        // Check for Boolean. Risk Validated by Triage
                        riskInstance.RiskValidatedByTriage = sqldataReader.GetBoolean(17);

                        if (!sqldataReader.IsDBNull(18))
                            riskInstance.CurrentCalculatedRiskValue = (int)sqldataReader.GetValue(18);
                        else
                            riskInstance.CurrentCalculatedRiskValue = 0; // TBD

                        riskInstance.TargetImpactValue = (int)sqldataReader.GetValue(19);

                        if (!sqldataReader.IsDBNull(20))
                            riskInstance.TargetImpactJustification = sqldataReader.GetString(20);
                        else
                            riskInstance.TargetImpactJustification = string.Empty;

                        riskInstance.TargetFrequencyValue = (int)sqldataReader.GetValue(21);

                        if (!sqldataReader.IsDBNull(22))
                            riskInstance.TargetFrequencyJustification = sqldataReader.GetString(22);
                        else
                            riskInstance.TargetFrequencyJustification = string.Empty;

                        riskInstance.TargetControlAffectivenessValue = (int)sqldataReader.GetValue(23);

                        if (!sqldataReader.IsDBNull(24))
                            riskInstance.TargetControlAffectivenessJustification = sqldataReader.GetString(24);
                        else
                            riskInstance.TargetControlAffectivenessJustification = string.Empty;

                        if (!sqldataReader.IsDBNull(25))
                            riskInstance.TargetRiskRating = sqldataReader.GetString(25);
                        else
                            riskInstance.TargetRiskRating = null;

                        if (!sqldataReader.IsDBNull(26))
                            riskInstance.TargetCalculatedRiskValue = (int)sqldataReader.GetValue(26);
                        else
                            riskInstance.TargetCalculatedRiskValue = 0; // TBD

                        if (!sqldataReader.IsDBNull(27))
                            riskInstance.OwnersComment = sqldataReader.GetString(27);
                        else
                            riskInstance.OwnersComment = string.Empty;

                        riskInstance.DataClassificationID = (int)sqldataReader.GetValue(28);
                        riskInstance.PIIID = (int)sqldataReader.GetValue(29);

                        if (!sqldataReader.IsDBNull(31))
                            riskInstance.UpdatedByFullName = sqldataReader.GetString(31);
                        else
                            riskInstance.UpdatedByFullName = string.Empty;

                        if (!sqldataReader.IsDBNull(30))
                            riskInstance.UpdatedByID = (int)sqldataReader.GetValue(30);
                        else
                            riskInstance.UpdatedByID = 0;

                        if (!sqldataReader.IsDBNull(32))
                            riskInstance.UpdatedDate = (DateTime)sqldataReader.GetValue(32);

                        if (!sqldataReader.IsDBNull(33))
                            riskInstance.RiskPriortizedWt = (Decimal)sqldataReader.GetValue(33);

                        if (!sqldataReader.IsDBNull(34))
                            riskInstance.RiskPriortizedCRV = (Decimal)sqldataReader.GetValue(34);
                        else
                            riskInstance.RiskPriortizedCRV = -1;
                    }

                    // OERM Classification.
                    if (sqldataReader.NextResult())
                    {
                        listOERMCollection = new Collection<int>();
                        while (sqldataReader.Read())
                        {
                            listOERMCollection.Add((int)sqldataReader.GetValue(0));
                        }
                        riskInstance.OERMClassification = listOERMCollection;
                    }
                    // read other strings for notes.
                    textForNotes = ReadResourceMessage.GetServiceMessage("SR_TextForNotes").ToString();
                    textForDelimiter = ReadResourceMessage.GetServiceMessage("SR_DelimiterNotes").ToString();
                    // Notes Classification.
                    if (sqldataReader.NextResult())
                    {
                        listnoteCollection = new Collection<NoteData>();
                        NoteData note = null;
                        while (sqldataReader.Read())
                        {
                            note = new NoteData();
                            note.Note = string.Empty;
                            note.SubmittedByFullName = sqldataReader.GetString(1);
                            note.CreatedDate = sqldataReader.GetValue(2).ToString();
                            listnoteCollection.Add(note);

                            note = null;
                            note = new NoteData();
                            note.Note = sqldataReader.GetString(3);
                            note.CreatedDate = string.Empty;
                            note.SubmittedByFullName = string.Empty;
                            listnoteCollection.Add(note);
                        }
                        riskInstance.Notes = listnoteCollection;
                    }

                    // Organizations
                    if (sqldataReader.NextResult())
                    {
                        if (sqldataReader.Read())
                        {
                            riskInstance.Organizations = sqldataReader.GetString(0);
                        }
                    }
                    // Contacts
                    if (sqldataReader.NextResult())
                    {
                        if (sqldataReader.Read())
                        {
                            riskInstance.Contacts = sqldataReader.GetString(0);
                        }
                    }
                    // XML + Orphaned Contacts
                    if (sqldataReader.NextResult())
                    {
                        organizationContact = new OrganizationContact();
                        if (sqldataReader.HasRows)
                        {
                            while (sqldataReader.Read())
                            {
                                if (!sqldataReader.IsDBNull(0))
                                {
                                    holdXML.Append(sqldataReader.GetString(0));
                                }
                            }
                        }

                        if (0 == holdXML.Length)
                            organizationContact.OrganizationContactXML = string.Empty;
                        else
                            organizationContact.OrganizationContactXML = holdXML.ToString();

                        // ALSO CHECK for Orphaned people.
                        if (sqldataReader.NextResult())
                        {
                            listPersonCollection = new Collection<Person>();
                            while (sqldataReader.Read())
                            {
                                personInstance = new Person();
                                personInstance.PersonID = (int)sqldataReader.GetValue(1);
                                personInstance.FullName = sqldataReader.GetString(0);
                                listPersonCollection.Add(personInstance);
                                personInstance = null;
                            }
                            organizationContact.OrphanedContacts = listPersonCollection;
                        }

                        riskInstance.OrganizationContact = organizationContact;
                    }

                    // Locations Classification.
                    if (sqldataReader.NextResult())
                    {
                        listRegionCollection = new Collection<int>();
                        while (sqldataReader.Read())
                        {
                            listRegionCollection.Add((int)sqldataReader.GetValue(0));
                        }
                        riskInstance.Geography = listRegionCollection;
                    }

                    // Risk Groups.
                    if (sqldataReader.NextResult())
                    {
                        listGroupsCollection = new Collection<int>();
                        while (sqldataReader.Read())
                        {
                            listGroupsCollection.Add((int)sqldataReader.GetValue(0));
                        }
                        riskInstance.RiskGroups = listGroupsCollection;
                    }

                    // Risk Orgin Application version ID.
                    if (sqldataReader.NextResult())
                    {
                        if (sqldataReader.Read())
                        {
                            if (!sqldataReader.IsDBNull(0))
                                riskInstance.RiskOriginApplicationID = sqldataReader.GetString(0);
                            else
                                riskInstance.RiskOriginApplicationID = string.Empty;
                        }
                    }
                } // HAS ROWS CLOSING 
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return riskInstance;
        }

        /// <summary>
        /// To update the existing risk.
        /// </summary>
        /// <param name="riskInstance"></param>
        /// <returns></returns>
        public static int UpdateRisk(RiskData riskInstance)
        {
            Database database = null;
            int result = -1;
            try
            {                
                // Check for risk is not null.
                if (riskInstance == null)
                    throw new ArgumentException("SR_20004");
                // Check for valid Risk ID.
                if (riskInstance.RiskID < 1)
                    throw new ArgumentException("SR_20001");

                // Check for Risk object validation using validation rules defined for Risk Object.
                ValidationResults validationResults = Validation.Validate<RiskData>(riskInstance, "RiskDataRules");
                if (!validationResults.IsValid)
                    throw new ArgumentException("SR_20003");

                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spUpdateRisk");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameter

                sqlCmd.Parameters.Add("@riskID", SqlDbType.Int).Value = riskInstance.RiskID;
                sqlCmd.Parameters.Add("@title", SqlDbType.NVarChar, 250).Value = riskInstance.Title;
                sqlCmd.Parameters.Add("@desc", SqlDbType.NVarChar).Value = riskInstance.Desc;
                sqlCmd.Parameters.Add("@currentRiskImapact", SqlDbType.Int).Value = riskInstance.CurrentImpactValue;
                sqlCmd.Parameters.Add("@currentRiskFrequency", SqlDbType.Int).Value = riskInstance.CurrentFrequencyValue;
                sqlCmd.Parameters.Add("@currentRiskCF", SqlDbType.Int).Value = riskInstance.CurrentControlAffectivenessValue;
                sqlCmd.Parameters.Add("@targetRiskImapact", SqlDbType.Int).Value = riskInstance.TargetImpactValue;
                sqlCmd.Parameters.Add("@targetRiskFrequency", SqlDbType.Int).Value = riskInstance.TargetFrequencyValue;
                sqlCmd.Parameters.Add("@targetRiskCF", SqlDbType.Int).Value = riskInstance.TargetControlAffectivenessValue;

                sqlCmd.Parameters.Add("@targetRiskrating", SqlDbType.NVarChar, 50).Value = riskInstance.TargetRiskRating;
                sqlCmd.Parameters.Add("@currentRiskrating", SqlDbType.NVarChar, 50).Value = riskInstance.CurrentRiskRating;

                // Target risk calculated value.
                if (riskInstance.TargetCalculatedRiskValue == 0)
                    sqlCmd.Parameters.Add("@targetCalculatedRiskValue", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@targetCalculatedRiskValue", SqlDbType.Int).Value = riskInstance.TargetCalculatedRiskValue;

                // Current risk calculated value.
                if (riskInstance.CurrentCalculatedRiskValue == 0)
                    sqlCmd.Parameters.Add("@currentCalculatedRiskValue", SqlDbType.Int).Value = null;
                else
                    sqlCmd.Parameters.Add("@currentCalculatedRiskValue", SqlDbType.Int).Value = riskInstance.CurrentCalculatedRiskValue;

                sqlCmd.Parameters.Add("@riskManagerID", SqlDbType.Int).Value = riskInstance.RiskManagerID;
                sqlCmd.Parameters.Add("@riskValidatedByTriage", SqlDbType.Int).Value = riskInstance.RiskValidatedByTriage;
                sqlCmd.Parameters.Add("@note", SqlDbType.NVarChar).Value = riskInstance.Note;
                sqlCmd.Parameters.Add("@oeramClassification", SqlDbType.NVarChar).Value = riskInstance.OERMClassifications;
                sqlCmd.Parameters.Add("@organizations", SqlDbType.NVarChar).Value = riskInstance.Organizations;
                sqlCmd.Parameters.Add("@locations", SqlDbType.NVarChar).Value = riskInstance.Locations;
                sqlCmd.Parameters.Add("@contacts", SqlDbType.NVarChar).Value = riskInstance.Contacts;
                sqlCmd.Parameters.Add("@Alias", SqlDbType.NVarChar, 50).Value = riskInstance.Alias;
                sqlCmd.Parameters.Add("@piid", SqlDbType.Int).Value = riskInstance.PIIID;
                sqlCmd.Parameters.Add("@dataClassfication", SqlDbType.Int).Value = riskInstance.DataClassificationID;
                sqlCmd.Parameters.Add("@Status", SqlDbType.Int).Value = riskInstance.Status;
                sqlCmd.Parameters.Add("@riskOwnerComment", SqlDbType.NVarChar).Value = riskInstance.OwnersComment;
                sqlCmd.Parameters.Add("@personID", SqlDbType.Int).Value = riskInstance.UpdatedBy;
                sqlCmd.Parameters.Add("@currentRiskImapactJustification", SqlDbType.NVarChar).Value = riskInstance.CurrentImapctJustification;
                sqlCmd.Parameters.Add("@currentRiskFrequencyJustification", SqlDbType.NVarChar).Value = riskInstance.CurrentFrequencyJustification;
                sqlCmd.Parameters.Add("@currentRiskCFJustification", SqlDbType.NVarChar).Value = riskInstance.CurrentControlAffectivenessJustification;
                sqlCmd.Parameters.Add("@targetRiskImapactJustification", SqlDbType.NVarChar).Value = riskInstance.TargetImpactJustification;
                sqlCmd.Parameters.Add("@targetRiskFrequencyJustification", SqlDbType.NVarChar).Value = riskInstance.TargetFrequencyJustification;
                sqlCmd.Parameters.Add("@targetRiskCFJustification", SqlDbType.NVarChar).Value = riskInstance.TargetControlAffectivenessJustification;
                sqlCmd.Parameters.Add("@riskGroups", SqlDbType.NVarChar).Value = riskInstance.RiskGroup;

                // CR 06/08/2009. Added new fields for prioritizationWt and prioritizationCRV.
                sqlCmd.Parameters.Add("@prioritizationWt", SqlDbType.Decimal).Value = riskInstance.RiskPriortizedWt;
                if (riskInstance.RiskPriortizedCRV != -1)
                    sqlCmd.Parameters.Add("@prioritizationCRV", SqlDbType.Decimal).Value = riskInstance.RiskPriortizedCRV;
                else
                    sqlCmd.Parameters.Add("@prioritizationCRV", SqlDbType.Decimal).Value = null;

                result = database.ExecuteNonQuery(sqlCmd);
            }
            catch (Exception exObject)
            {                
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if(string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (database != null)
                    database = null;
            }
            return result;
        }

        /// <summary>
        /// To get the actual data records based on filter criteria specified.
        /// </summary>
        /// <param name="fieldName"></param>
        /// <param name="fieldoperator"></param>
        /// <param name="sortField"></param>
        /// <param name="sortAscDesc"></param>
        /// <param name="pageSize"></param>
        /// <param name="startIndex"></param>
        /// <param name="value1"></param>
        /// <param name="value2"></param>
        /// <param name="personID"></param>
        /// <param name="isRoleFilter"></param>
        /// <returns></returns>
        public static
        Collection<RiskInformation> BasicSearchRisk(string fieldName, string fieldoperator, string sortField, int sortAscDesc, int pageSize, int startIndex, string value1, string value2, int personID, int isRoleFilter)
        {
            RiskInformation riskInstance = null;
            Collection<RiskInformation> riskInformationCollection = null;
            SqlDataReader sqldataReader = null;
            Database database = null;
            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRisksByFilter");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameters
                sqlCmd.Parameters.Add("@fieldName", SqlDbType.NVarChar, 50).Value = fieldName;
                sqlCmd.Parameters.Add("@fieldOperator", SqlDbType.NVarChar, 50).Value = fieldoperator;
                sqlCmd.Parameters.Add("@sortField", SqlDbType.NVarChar, 50).Value = sortField;
                sqlCmd.Parameters.Add("@sortDescending", SqlDbType.Int).Value = sortAscDesc;
                sqlCmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
                sqlCmd.Parameters.Add("@startRowIndex", SqlDbType.Int).Value = startIndex;
                sqlCmd.Parameters.Add("@value1", SqlDbType.NVarChar, 250).Value = value1;
                sqlCmd.Parameters.Add("@value2", SqlDbType.NVarChar, 250).Value = value2;
                sqlCmd.Parameters.Add("@personID", SqlDbType.Int).Value = personID;
                sqlCmd.Parameters.Add("@isRoleFilter", SqlDbType.Int).Value = isRoleFilter;

                sqldataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);
                riskInformationCollection = new Collection<RiskInformation>();

                while (sqldataReader.Read())
                {
                    riskInstance = new RiskInformation();
                    riskInstance.Title = sqldataReader.GetString(2);
                    riskInstance.RiskID = (int)sqldataReader.GetValue(1);
                    riskInstance.CreatedDate = (DateTime)sqldataReader.GetValue(3);
                    riskInstance.StatusText = sqldataReader.GetString(7);
                    riskInstance.SubmittedByFullName = sqldataReader.GetString(6);

                    if (!sqldataReader.IsDBNull(5))
                        riskInstance.CurrentRiskRating = sqldataReader.GetString(5);
                    else
                        riskInstance.CurrentRiskRating = "UnKnown";

                    if (!sqldataReader.IsDBNull(8))
                        riskInstance.CurrentCalculatedRiskValue = (int)sqldataReader.GetValue(8);
                    else
                        riskInstance.CurrentCalculatedRiskValue = -1; // -1 is not supported value for CRV.

                    // Uncomment it if required to support PCRV groups.
                    if (!sqldataReader.IsDBNull(9))
                        riskInstance.RiskPriortizedCRV = (Decimal)sqldataReader.GetValue(9);
                    else
                        riskInstance.RiskPriortizedCRV = -1;

                    riskInformationCollection.Add(riskInstance);
                    riskInstance = null;
                }
            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return riskInformationCollection;
        }

        /// <summary>
        /// To get the number of records count based on filter criteria.
        /// </summary>
        /// <param name="fieldName"></param>
        /// <param name="fieldoperator"></param>
        /// <param name="value1"></param>
        /// <param name="value2"></param>
        /// <param name="personID"></param>
        /// <param name="isRoleFilter"></param>
        /// <returns></returns>
        public static
        int BasicSearchRiskCount(string fieldName, string fieldoperator, string value1, string value2, int personID, int isRoleFilter)
        {
            SqlDataReader sqldataReader = null;
            Database database = null;
            int result = -1;
            try
            {
                database = DatabaseFactory.CreateDatabase("RMDB");
                SqlCommand sqlCmd = new SqlCommand("ISRM_spGetRisksByFilterCount");
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // Set Parameters
                sqlCmd.Parameters.Add("@fieldName", SqlDbType.NVarChar, 50).Value = fieldName;
                sqlCmd.Parameters.Add("@fieldOperator", SqlDbType.NVarChar, 50).Value = fieldoperator;
                sqlCmd.Parameters.Add("@value1", SqlDbType.NVarChar, 250).Value = value1;
                sqlCmd.Parameters.Add("@value2", SqlDbType.NVarChar, 250).Value = value2;
                sqlCmd.Parameters.Add("@personID", SqlDbType.Int).Value = personID;
                sqlCmd.Parameters.Add("@isRoleFilter", SqlDbType.Int).Value = isRoleFilter;

                result = (int)database.ExecuteScalar(sqlCmd);

            }
            catch (Exception exObject)
            {
                //Check database message.
                string messages = ReadResourceMessage.GetDatabaseMessage(exObject.Message);
                if (string.IsNullOrEmpty(messages))
                    messages = ReadResourceMessage.GetServiceMessage(exObject.Message);

                //Check service message.
                if (!string.IsNullOrEmpty(messages))
                    throw new Exception("Custom:" + messages, exObject);
                else
                    throw;
            }
            finally
            {
                if (sqldataReader != null)
                    sqldataReader.Close();

                if (database != null)
                    database = null;
            }
            return result;
        }
    }
}
